-- ** 创建者   : suning
-- ** 创建日期 : 20221030
-- ** 功能描述 ：动静对比-全链路判责-静态环节明细表
-- **************************************************************************
-- **************************** 修改日志 ************************************
-- **************************************************************************
-- ** 依赖表：
--jms_dm.dm_whole_decide_duty_dynamic_dt    动态环节明细表
--jms_dm.dm_route_whole_step_dt             静态路由轨迹表
--jms_dim.dim_tmsnew_vehicle_line           车线表
-- alter table add columns 20230418
-- ,cainiao_plan_sign_time              timestamp         comment'达成率菜鸟规划签收时间'
-- ,ziyou_plan_sign_time                timestamp         comment'达成率自有规划签收时间'
-- **************************************************************************
-- *******************  CURRENT_VERSION ： V.1.1  ***************************
drop table jms_dm.dm_whole_decide_duty_static_dt
create external table if not exists jms_dm.dm_whole_decide_duty_static_dt(
    waybill_no                          string            comment'运单号'
   ,route_id                            string            comment'路由唯一键'
   ,total_days                          string            comment'T+时效'
   ,first_network_code                  string            comment'始发网点编码'
   ,first_network_name                  string            comment'始发网点名称'
   ,sign_network_code                   string            comment'签收网点编码'
   ,sign_network_name                   string            comment'签收网点名称'
   ,last_network_code                   string            comment'目的网点编码'
   ,last_network_name                   string            comment'目的网点名称'
   ,scan_site_plan_no                   bigint            comment'当前站点编号'
   ,scan_site_plan_code                 string            comment'当前站点编码'
   ,scan_site_plan_name                 string            comment'当前站点名称'
   ,scan_site_plan_type                 string            comment'当前站点类型'
   ,scan_site_plan_simple_name          string            comment'当前站点简称'
   ,scan_site_plan_send_time            timestamp         comment'当前站点规划发车时间'
   ,scan_site_plan_arrival_time         timestamp         comment'当前站点规划到车时间'
   ,scan_site_plan_last_arrival_time    timestamp         comment'当前站点规划最晚到车时间'
   ,scan_site_plan_last_send_time       timestamp         comment'当前站点规划最晚发车时间'
   ,plan_diff_time                      string            comment'当前站点与下一站点时间差'
   ,plan_last_taking_time               timestamp         comment'当前站点规划最晚揽收结束时间(h)'
   ,plan_deliver_time                   timestamp         comment'目的网点规划派件时间'
   ,plan_sign_time                      timestamp         comment'目的网点规划签收时间'
   ,pre_site_plan_send_time             timestamp         comment'上一站点规划发车时间'
   ,pre_site_plan_arrival_time          timestamp         comment'上一站点规划到车时间'
   ,pre_site_plan_last_send_time        timestamp         comment'上一站点规划最晚发车时间'
   ,pre_site_plan_last_arrival_time     timestamp         comment'上一站点规划最晚到车时间'
   ,next_site_plan_no                   bigint            comment'下一站点编号'
   ,next_site_plan_code                 string            comment'下一站点编码'
   ,next_site_plan_name                 string            comment'下一站点名称'
   ,next_site_plan_type                 string            comment'下一站点类型'
   ,next_l2_site_code                   string            comment'下下一站点编码'
   ,next_l3_site_code                   string            comment'下下下一站点编码'
   ,next_l4_site_code                   string            comment'下下下下一站点编码'
   ,next_site_plan_send_time            timestamp         comment'下一站点规划发车时间'
   ,next_site_plan_arrival_time         timestamp         comment'下一站点规划到车时间'
   ,next_site_plan_last_send_time       timestamp         comment'下一站点规划最晚发车时间'
   ,next_site_plan_last_arrival_time    timestamp         comment'下一站点规划最晚到车时间'
   ,first_center_plan_send_time         timestamp         comment'始发中心规划发车时间'
   ,first_center_plan_arrival_time      timestamp         comment'始发中心规划到车时间'
   ,first_center_plan_last_send_time    timestamp         comment'始发中心规划最晚发车时间'
   ,first_center_plan_last_arrival_time timestamp         comment'始发中心规划最晚到车时间'
   ,plan_node                           string            comment'规划节点'
   ,scan_site_oper_time                 bigint            comment'当前站点操作时长'
   ,next_site_plan_oper_time            bigint            comment'下一站点规划操作时长'
   ,scan_site_plan_line_name            string            comment'当前站点规划车线'
   ,cainiao_plan_sign_time              timestamp         comment'达成率菜鸟规划签收时间'
   ,ziyou_plan_sign_time                timestamp         comment'达成率自有规划签收时间'
) comment '经营指标-全链路判责-静态环节'
partitioned by (dt string comment '分区日期')
stored as parquet
location '/dw/hive/jms_dm.db/external/dm_whole_decide_duty_static_dt'
tblproperties (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);